Window Aggregate Functions
Table of Contents
1. COUNT
1.1 Count total orders and orders per customer
Task: Find for each order:
- the total number of orders overall, and
- the total number of orders per customer.
Show OrderID, OrderDate, CustomerID, total orders, and orders by customer.
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
CustomerID,
COUNT(*) OVER() AS TotalOrders,
COUNT(*) OVER(PARTITION BY CustomerID) AS OrdersByCustomers
FROM Sales.Orders
1.2 Count customers, scores, and countries
Task:
Using Sales.Customers, calculate:
- total number of customers using
COUNT(*) - total number of customers using
COUNT(1) - total number of (non-NULL) scores
- total number of (non-NULL) countries
Return all customer columns plus these four counts.
💡 Suggested Answers
SELECT
*,
COUNT(*) OVER () AS TotalCustomersStar,
COUNT(1) OVER () AS TotalCustomersOne,
COUNT(Score) OVER() AS TotalScores,
COUNT(Country) OVER() AS TotalCountries
FROM Sales.Customers
1.3 Check for duplicate rows in OrdersArchive
Task:
Check whether the table Sales.OrdersArchive contains any duplicate rows based on OrderID.
Return only those rows that are duplicates.
💡 Suggested Answers
SELECT
*
FROM (
SELECT
*,
COUNT(*) OVER(PARTITION BY OrderID) AS CheckDuplicates
FROM Sales.OrdersArchive
) t
WHERE CheckDuplicates > 1
2. SUM
2.1 Total sales overall and by product
Task: For each order, show:
- total sales across all orders, and
- total sales per product.
Include OrderID, OrderDate, Sales, and ProductID.
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
Sales,
ProductID,
SUM(Sales) OVER () AS TotalSales,
SUM(Sales) OVER (PARTITION BY ProductID) AS SalesByProduct
FROM Sales.Orders
2.2 Percentage contribution of each sale to total sales
Task:
For each order, calculate what percentage of the total sales this order’s Sales value represents.
Show OrderID, ProductID, Sales, total sales, and the percentage (rounded to 2 decimals).
💡 Suggested Answers
SELECT
OrderID,
ProductID,
Sales,
SUM(Sales) OVER () AS TotalSales,
ROUND(CAST(Sales AS FLOAT) / SUM(Sales) OVER () * 100, 2) AS PercentageOfTotal
FROM Sales.Orders
3. AVG
3.1 Average sales overall and by product
Task: For each order, compute:
- average sales across all orders, and
- average sales per product.
Show OrderID, OrderDate, Sales, ProductID, and the two averages.
💡 Suggested Answers
SELECT
OrderID,
OrderDate,
Sales,
ProductID,
AVG(Sales) OVER () AS AvgSales,
AVG(Sales) OVER (PARTITION BY ProductID) AS AvgSalesByProduct
FROM Sales.Orders
3.2 Average customer scores with and without NULL handling
Task:
Using Sales.Customers, calculate:
- the average of
Score(default behavior, NULLs excluded), and - the average when NULL scores are treated as 0 using
COALESCE.
Show CustomerID, LastName, Score, the score with NULLs replaced by 0, and both averages.
💡 Suggested Answers
SELECT
CustomerID,
LastName,
Score,
COALESCE(Score, 0) AS CustomerScore,
AVG(Score) OVER () AS AvgScore,
AVG(COALESCE(Score, 0)) OVER () AS AvgScoreWithoutNull
FROM Sales.Customers
3.3 Orders with sales above the overall average
Task:
Find all orders where Sales exceed the average Sales across all orders.
Use a window function in a subquery to compute the average.
💡 Suggested Answers
SELECT
*
FROM (
SELECT
OrderID,
ProductID,
Sales,
AVG(Sales) OVER () AS Avg_Sales
FROM Sales.Orders
) t
WHERE Sales > Avg_Sales
4. MAX / MIN
4.1 Highest and lowest sales across all orders
Task:
Find the minimum and maximum Sales values across all rows in Sales.Orders.
💡 Suggested Answers
SELECT
MIN(Sales) AS MinSales,
MAX(Sales) AS MaxSales
FROM Sales.Orders
4.2 Lowest sales overall and by product
Task: For each order, show:
- the lowest Sales value across all orders, and
- the lowest Sales per Product.
Include OrderID, ProductID, OrderDate, Sales, and the two minimums.
💡 Suggested Answers
SELECT
OrderID,
ProductID,
OrderDate,
Sales,
MIN(Sales) OVER () AS LowestSales,
MIN(Sales) OVER (PARTITION BY ProductID) AS LowestSalesByProduct
FROM Sales.Orders
4.3 Employees with the highest salary
Task:
From Sales.Employees, return only the employees who have the highest salary.
Use a window function to find the maximum salary.
__
💡 Suggested Answers
SELECT *
FROM (
SELECT *,
MAX(Salary) OVER() AS HighestSalary
FROM Sales.Employees
) t
WHERE Salary = HighestSalary
</details>
---
### 4.4 Deviation from minimum and maximum sales
**Task:**
For each order, calculate:
* the **highest Sales** across all orders
* the **lowest Sales** across all orders
* the **difference from the minimum** (`Sales - LowestSales`)
* the **difference from the maximum** (`HighestSales - Sales`)
Show `OrderID`, `OrderDate`, `ProductID`, `Sales`, and these extra columns.
<details>
<summary>💡 Suggested Answers</summary>
```sql
SELECT
OrderID,
OrderDate,
ProductID,
Sales,
MAX(Sales) OVER () AS HighestSales,
MIN(Sales) OVER () AS LowestSales,
Sales - MIN(Sales) OVER () AS DeviationFromMin,
MAX(Sales) OVER () - Sales AS DeviationFromMax
FROM Sales.Orders
5. ROLLING SUM & AVERAGE Use Case
5.1 Moving average of sales per product over time
Task:
For each order, calculate the moving average of Sales per Product over time, ordered by OrderDate.
Also show the simple average per product (ignoring order of dates).
💡 Suggested Answers
SELECT
OrderID,
ProductID,
OrderDate,
Sales,
AVG(Sales) OVER (PARTITION BY ProductID) AS AvgByProduct,
AVG(Sales) OVER (PARTITION BY ProductID ORDER BY OrderDate) AS MovingAvg
FROM Sales.Orders
5.2 Rolling average including current and next order only
Task: For each order, calculate a rolling average of Sales per Product that includes only:
- the current order, and
- the next order (1 following row)
within each product partition, ordered by OrderDate.
💡 Suggested Answers
SELECT
OrderID,
ProductID,
OrderDate,
Sales,
AVG(Sales) OVER (PARTITION BY ProductID ORDER BY OrderDate ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS RollingAvg
FROM Sales.Orders